﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;

namespace LibFriesland_Campina_CRM
{
    class DalProductInShopDiary
    {
        //Cap nhat so luong san pham cua cua hang trong kho
        public static int updateProductQuantityInShopDiary(string pBy_product, string pBy_shop, DateTime pDatetime, int pQuantity)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                DalBase obj = new DalBase();
                string commandText = String.Format("update product_in_shop_diary set quantity = quantity + {0} where by_product = '{1}' and by_shop = '{2}' and datetime_diary = '{3}'", pQuantity, pBy_product, pBy_shop, pDatetime.Date.ToString("yyyy-MM-dd"));
                return obj.executeNonqueryCommand(commandText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region Dung cho Webservice
        //Cap nhat gia ban le cua san pham tai cua hang
        public static int updateProductPriceInShopDiary(string pBy_shop, string pBy_product, DateTime pDatetime, decimal pPrice)
        {
            try
            {
                DalBase obj = new DalBase();
                string commandText = String.Format("update product_in_shop_diary set price = '{0}' where by_product = '{1}' and by_shop = '{2}' and datetime_diary = '{3}'", pPrice, pBy_product, pBy_shop, pDatetime.Date.ToString("yyyy-MM-dd"));
                obj.executeNonqueryCommand(commandText);
                BizProductInShop.updateProductPriceInShop(pBy_product, pBy_shop, pPrice); //Cap nhat gia san pham tai cua hang
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
        #endregion

        #region Dung cho Report
        //Thong ke so luong san pham ton cua cua hang theo ngay
        public static Dataset.DatasetProductInShop getProductQuantityInShopbyDate(DateTime pDatetime)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            Dataset.DatasetProductInShop ds = new Dataset.DatasetProductInShop();
            DalBase dal = new DalBase();

            #region Tao du lieu cho bang Shop
            string commandText = "select shop_charid, name as shop_name from shop where shop_charid <> 'SHDEFAULT'";
            DataTable shopTable = dal.getData(commandText);
            foreach (DataRow item in shopTable.Rows)
            {
                ds.Tables["SHOP"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_CATALOGUE
            commandText = "select product_catalogue_charid, short_name as product_catalogue_name, by_product_brand from product_catalouge";
            DataTable productCatalogue = dal.getData(commandText);
            foreach (DataRow item in productCatalogue.Rows)
            {
                ds.Tables["PRODUCT_CATALOGUE"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND
            commandText = "select product_brand_charid, name as product_brand_name from product_brand";
            DataTable productBrand = dal.getData(commandText);
            foreach (DataRow item in productBrand.Rows)
            {
                ds.Tables["PRODUCT_BRAND"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT
            commandText = " SELECT product_charid ,product.short_name as product_name, "
                        + " by_product_catalogue "
                        + " FROM product ";
            DataTable productTable = dal.getData(commandText);
            foreach (DataRow item in productTable.Rows)
            {
                ds.Tables["PRODUCT"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_IN_SHOP
            string datetimeStr = pDatetime.ToString("yyyy-MM-dd");
            commandText = " select by_product,quantity, by_shop "
                        + " from product_in_shop_diary "
                        + " where DATE(datetime_diary) = '" + datetimeStr + "'";

            DataTable productInShopTable = dal.getData(commandText);
            foreach (DataRow item in productInShopTable.Rows)
            {
                ds.Tables["PRODUCT_IN_SHOP"].ImportRow(item);
            }
            #endregion

            return ds;
        }

        //Thong ke gia ban le san pham tai cua hang
        public static Dataset.DatasetPriceAudit getPriceAudit(DateTime pDatetime)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            Dataset.DatasetPriceAudit ds = new Dataset.DatasetPriceAudit();
            DalBase dal = new DalBase();

            #region Tao du lieu cho bang Shop
            string commandText = "select shop_charid, name as shop_name from shop where shop_charid <> 'SHDEFAULT'";
            DataTable shopTable = dal.getData(commandText);
            foreach (DataRow item in shopTable.Rows)
            {
                ds.Tables["SHOP"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_CATALOGUE
            commandText = "select product_catalogue_charid, short_name as product_catalogue_name, by_product_brand from product_catalouge";
            DataTable productCatalogue = dal.getData(commandText);
            foreach (DataRow item in productCatalogue.Rows)
            {
                ds.Tables["PRODUCT_CATALOGUE"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND
            commandText = "select product_brand_charid, name as product_brand_name from product_brand";
            DataTable productBrand = dal.getData(commandText);
            foreach (DataRow item in productBrand.Rows)
            {
                ds.Tables["PRODUCT_BRAND"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT
            commandText = " SELECT product_charid ,product.short_name as product_name, "
                        + " by_product_catalogue, price "
                        + " FROM product ";
            DataTable productTable = dal.getData(commandText);
            foreach (DataRow item in productTable.Rows)
            {
                ds.Tables["PRODUCT"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_IN_SHOP
            string datetimeStr = pDatetime.ToString("yyyy-MM-dd");
            commandText = " select by_product,quantity, by_shop, price, price_by_company "
                        + " from product_in_shop_diary "
                        + " where DATE(datetime_diary) = '" + datetimeStr + "'";

            DataTable productInShopTable = dal.getData(commandText);
            foreach (DataRow item in productInShopTable.Rows)
            {
                ds.Tables["PRODUCT_IN_SHOP"].ImportRow(item);
            }
            #endregion

            return ds;
        }
        #endregion
    }
}
